package com.zxb.base.generator.service.impl;

import cn.hutool.core.util.StrUtil;
import com.zxb.base.generator.enm.ColumnTypeEnum;
import com.zxb.base.generator.service.DdlService;
import com.zxb.base.generator.vo.ColumnVo;
import com.zxb.base.generator.vo.PublicColumnExistVo;
import com.zxb.base.generator.vo.TreeColumnExistVo;
import com.zxb.core.common.R;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Zxb
 */
@Service
@AllArgsConstructor
@Slf4j
public class OracleDdlServiceImpl implements DdlService {

    private JdbcTemplate jdbcTemplate;

    @Override
    public Boolean isTableExist(String schema, String tableName) {
        String sql = "select count(*) from user_tables where tablespace_name = ? and table_name = ?";
        Object[] args = new Object[]{schema.toUpperCase(), tableName.toUpperCase()};
        int count = jdbcTemplate.queryForObject(sql, Integer.class, args);
        return count > 0;
    }

    @Override
    public List<String> findTable(String schema) {
        String sql = "select table_name from user_tables where tablespace_name = ?";
        Object[] args = new Object[]{schema.toUpperCase()};
        return jdbcTemplate.queryForList(sql, String.class, args);
    }

    @Override
    public List<String> findTableWithPrefix(String schema, String prefix) {
        String sql = "select table_name from user_tables where tablespace_name = ? and table_name like ?";
        Object[] args = new Object[]{schema.toUpperCase(), prefix + "%"};
        return jdbcTemplate.queryForList(sql, String.class, args);
    }

    @Override
    public List<ColumnVo> findColumnByTable(String tableName, String schema) {
        String sql = " select a.column_name,a.data_type,a.data_precision,a.data_scale,b.comments as column_comment " +
                " from user_tab_columns a " +
                " left join user_col_comments b on a.table_name = b.table_name and a.column_name = b.column_name " +
                " where a.table_name=? ";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            ColumnVo column = new ColumnVo();
            String name = rs.getString("column_name");
            String comment = rs.getString("column_comment");
            String type = rs.getString("data_type");
            if (type.equals("NUMBER")) {
                String precision = rs.getString("data_precision");
                String scale = rs.getString("data_scale");
                if (StrUtil.isNotBlank(precision)) {
                    if (StrUtil.isNotBlank(scale) && !scale.equals("0")) {
                        type = "NUMBER(" + precision + "," + scale + ")";
                    } else {
                        type = "NUMBER(" + precision + ")";
                    }
                }
            } else if (type.equals("DATE")) {
                if (comment.startsWith("date:")) {
                    type = "DATE(date)";
                } else if (comment.startsWith("time:")) {
                    type = "DATE(time)";
                } else {
                    type = "DATE(datetime)";
                }
            }
            column.setName(name);
            column.setComment(comment);
            column.setType(type);
            return column;
        }, tableName);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> dropColumn(String schema, String tableName, String columnName) {
        String sql1 = "alter table " + tableName + " DROP (" + columnName + ")";
        String sql2 = "delete from GEN_COLUMN where TABLE_NAME='" + tableName + "' and COLUMN_NAME = '" + columnName + "'";
        jdbcTemplate.batchUpdate(sql1, sql2);
        return R.success();
    }

    private PublicColumnExistVo validatePublicColumn(String schema, String tableName) {
        PublicColumnExistVo vo = new PublicColumnExistVo();
        List<ColumnVo> columnList = findColumnByTable(tableName, schema);
        if (columnList != null && !columnList.isEmpty()) {
            for (ColumnVo column : columnList) {
                String colName = column.getName();
                if ("CREATE_TIME".equals(colName)) {
                    vo.setHasCreateTime(true);
                } else if ("CREATE_BY".equals(colName)) {
                    vo.setHasCreateBy(true);
                } else if ("UPDATE_TIME".equals(colName)) {
                    vo.setHasUpdateTime(true);
                } else if ("UPDATE_BY".equals(colName)) {
                    vo.setHasUpdateBy(true);
                } else if ("VER".equals(colName)) {
                    vo.setHasVer(true);
                } else if ("IS_DEL".equals(colName)) {
                    vo.setHasIsDel(true);
                } else if ("SEARCH_STR".equals(colName)) {
                    vo.setHasSearchStr(true);
                }
            }
        }
        return vo;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void addPubColumn(String schema, String tableName) {
        log.info("开始处理表>>>>" + tableName);
        PublicColumnExistVo vo = validatePublicColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();

        if (!vo.isHasCreateTime()) {
            log.info("开始生成字段>>>>" + tableName + ":create_time");
            String sql1 = "alter table \"" + tableName + "\" add (\"CREATE_TIME\" DATE)";
            String sql2 = "comment on column \"" + tableName + "\".\"CREATE_TIME\"" + " is '数据创建时间'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":create_time");
        }
        if (!vo.isHasCreateBy()) {
            log.info("开始生成字段>>>>" + tableName + ":create_by");
            String sql1 = "alter table \"" + tableName + "\" add (\"CREATE_BY\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"CREATE_BY\"" + " is '数据创建者'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":create_by");
        }
        if (!vo.isHasUpdateTime()) {
            log.info("开始生成字段>>>>" + tableName + ":update_time");
            String sql1 = "alter table \"" + tableName + "\" add (\"UPDATE_TIME\" DATE)";
            String sql2 = "comment on column \"" + tableName + "\".\"UPDATE_TIME\"" + " is '数据更新时间'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":update_time");
        }
        if (!vo.isHasUpdateBy()) {
            log.info("开始生成字段>>>>" + tableName + ":update_by");
            String sql1 = "alter table \"" + tableName + "\" add (\"UPDATE_BY\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"UPDATE_BY\"" + " is '数据更新者'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":update_by");
        }
        if (!vo.isHasVer()) {
            log.info("开始生成字段>>>>" + tableName + ":ver");
            String sql1 = "alter table \"" + tableName + "\" add (\"VER\" NUMBER(11))";
            String sql2 = "comment on column \"" + tableName + "\".\"VER\"" + " is '乐观锁'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":ver");
        }
        if (!vo.isHasIsDel()) {
            log.info("开始生成字段>>>>" + tableName + ":is_del");
            String sql1 = "alter table \"" + tableName + "\" add (\"IS_DEL\" NUMBER(4))";
            String sql2 = "comment on column \"" + tableName + "\".\"IS_DEL\"" + " is '是否删除'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":is_del");
        }
        if (!vo.isHasSearchStr()) {
            log.info("开始生成字段>>>>" + tableName + ":search_str");
            String sql1 = "alter table \"" + tableName + "\" add (\"SEARCH_STR\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"SEARCH_STR\"" + " is '查询字符串'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":search_str");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.info("结束处理表>>>>" + tableName);
    }

    private TreeColumnExistVo validateTreeColumn(String schema, String tableName) {
        TreeColumnExistVo vo = new TreeColumnExistVo();
        List<ColumnVo> columnList = findColumnByTable(tableName, schema);
        if (columnList != null && !columnList.isEmpty()) {
            for (ColumnVo column : columnList) {
                String colName = column.getName();
                if ("NAME".equals(colName)) {
                    vo.setHasName(true);
                } else if ("CODE".equals(colName)) {
                    vo.setHasCode(true);
                } else if ("LAYER".equals(colName)) {
                    vo.setHasLayer(true);
                } else if ("PID".equals(colName)) {
                    vo.setHasPid(true);
                } else if ("NAME_LINK".equals(colName)) {
                    vo.setHasNameLink(true);
                } else if ("ID_Link".equals(colName)) {
                    vo.setHasIdLink(true);
                }
            }
        }
        return vo;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void addTreeColumn(String schema, String tableName) {
        log.info("开始处理表>>>>" + tableName);
        TreeColumnExistVo vo = validateTreeColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();

        if (!vo.isHasName()) {
            log.info("开始生成字段>>>>" + tableName + ":name");
            String sql1 = "alter table \"" + tableName + "\" add (\"NAME\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"NAME\"" + " is '名称'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":name");
        }
        if (!vo.isHasCode()) {
            log.info("开始生成字段>>>>" + tableName + ":code");
            String sql1 = "alter table \"" + tableName + "\" add (\"CODE\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"CODE\"" + " is '编码'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":code");
        }
        if (!vo.isHasLayer()) {
            log.info("开始生成字段>>>>" + tableName + ":layer");
            String sql1 = "alter table \"" + tableName + "\" add (\"LAYER\" NUMBER(11))";
            String sql2 = "comment on column \"" + tableName + "\".\"LAYER\"" + " is '层级'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":layer");
        }
        if (!vo.isHasPid()) {
            log.info("开始生成字段>>>>" + tableName + ":pid");
            String sql1 = "alter table \"" + tableName + "\" add (\"PID\" NUMBER(20))";
            String sql2 = "comment on column \"" + tableName + "\".\"PID\"" + " is '上级编号'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":pid");
        }
        if (!vo.isHasNameLink()) {
            log.info("开始生成字段>>>>" + tableName + ":nameLink");
            String sql1 = "alter table \"" + tableName + "\" add (\"NAME_LINK\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"NAME_LINK\"" + " is '全链路名称'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":nameLink");
        }
        if (!vo.isHasIdLink()) {
            log.info("开始生成字段>>>>" + tableName + ":idLink");
            String sql1 = "alter table \"" + tableName + "\" add (\"ID_LINK\" NVARCHAR2(255))";
            String sql2 = "comment on column \"" + tableName + "\".\"ID_LINK\"" + " is '全链路编号'";
            sqlList.add(sql1);
            sqlList.add(sql2);
            log.info("结束生成字段>>>>" + tableName + ":idLink");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.info("结束处理表>>>>" + tableName);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void dropPubColumn(String schema, String tableName) {
        log.info("开始处理表>>>>" + tableName);
        PublicColumnExistVo vo = validatePublicColumn(schema, tableName);
        List<String> sqlList = new ArrayList<>();
        if (vo.isHasCreateTime()) {
            log.info("开始删除字段>>>>" + tableName + ":create_time");
            String sql = "alter table \"" + tableName + "\" DROP (\"CREATE_TIME\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":create_time");
        }
        if (vo.isHasCreateBy()) {
            log.info("开始删除字段>>>>" + tableName + ":create_by");
            String sql = "alter table \"" + tableName + "\" DROP (\"CREATE_BY\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":create_by");
        }
        if (vo.isHasUpdateTime()) {
            log.info("开始删除字段>>>>" + tableName + ":update_time");
            String sql = "alter table \"" + tableName + "\" DROP (\"UPDATE_TIME\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":update_time");
        }
        if (vo.isHasUpdateBy()) {
            log.info("开始删除字段>>>>" + tableName + ":update_by");
            String sql = "alter table \"" + tableName + "\" DROP (\"UPDATE_BY\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":update_by");
        }
        if (vo.isHasVer()) {
            log.info("开始删除字段>>>>" + tableName + ":ver");
            String sql = "alter table \"" + tableName + "\" DROP (\"VUE\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":ver");
        }
        if (vo.isHasIsDel()) {
            log.info("开始删除字段>>>>" + tableName + ":is_del");
            String sql = "alter table \"" + tableName + "\" DROP (\"IS_DEL\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":is_del");
        }
        if (vo.isHasSearchStr()) {
            log.info("开始删除字段>>>>" + tableName + ":search_str");
            String sql = "alter table \"" + tableName + "\" DROP (\"SEARCH_STR\")";
            sqlList.add(sql);
            log.info("结束删除字段>>>>" + tableName + ":search_str");
        }
        if (sqlList.size() > 0) {
            String[] sqlArr = new String[sqlList.size()];
            jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        }
        log.info("结束处理表>>>>" + tableName);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> addTable(String schema, String tableName, List<ColumnVo> columnVoList) {
        StringBuilder sb = new StringBuilder();
        List<String> sqlList = new ArrayList<>();

        sb.append(" create table \"").append(tableName).append("\" ( ");
        sb.append(" \"ID\" NUMBER(20), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"ID\" IS '编号'");
        for (ColumnVo columnVo : columnVoList) {
            String name = columnVo.getName();
            String type = columnVo.getType();
            String comment = columnVo.getComment();
            sb.append(" \"" + name.toUpperCase() + "\" " + ColumnTypeEnum.getOracleType(type) + ", ");
            comment = type + ":" + comment;
            sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"" + name.toUpperCase() + "\" IS '" + comment + "'");
        }
        sb.append(" \"CREATE_TIME\" DATE, ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"CREATE_TIME\" IS '数据创建时间'");
        sb.append(" \"CREATE_BY\" NVARCHAR2(50), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"CREATE_BY\" IS '数据创建者'");
        sb.append(" \"UPDATE_TIME\" DATE, ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"UPDATE_TIME\" IS '数据更新时间'");
        sb.append(" \"UPDATE_BY\" NVARCHAR2(50), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"UPDATE_BY\" IS '数据更新者'");
        sb.append(" \"VER\" NUMBER(11), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"VER\" IS '乐观锁'");
        sb.append(" \"IS_DEL\" NUMBER(4), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"IS_DEL\" IS '是否删除'");
        sb.append(" \"SEARCH_STR\" NVARCHAR2(255), ");
        sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"SEARCH_STR\" IS '查询字符串'");
        sb.append(" PRIMARY KEY (\"ID\") ");
        sb.append(" ) ");
        String sql = sb.toString();
        jdbcTemplate.batchUpdate(sql);
        String[] sqlArr = new String[sqlList.size()];
        jdbcTemplate.batchUpdate(sqlList.toArray(sqlArr));
        return R.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> delTable(String schema, String tableName) {
        String sql1 = " drop table " + tableName;
        String sql2 = " delete from GEN_COLUMN where TABLE_NAME='" + tableName + "'";
        jdbcTemplate.batchUpdate(sql1, sql2);
        return R.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> mergeColumnsForTable(String schema, String tableName, List<ColumnVo> columnVoList) {
        List<ColumnVo> list = findColumnByTable(tableName, schema);
        List<String> sqlList = new ArrayList<>();
        for (ColumnVo columnVo : columnVoList) {
            String name = columnVo.getName();
            String type = columnVo.getName();
            String comment = columnVo.getType();
            StringBuilder sqlSb = new StringBuilder(" ALTER TABLE \"" + tableName + "\" ");
            if (list.stream().anyMatch(t -> t.getName().equals(name))) {
                String str = " MODIY (\"" + name.toUpperCase() + "\" ";
                sqlSb.append(str);
            } else {
                String str = " ADD (\"" + name.toUpperCase() + "\" ";
                sqlSb.append(str);
            }
            sqlSb.append(" " + ColumnTypeEnum.getOracleType(type) + " ) ");
            sqlList.add(sqlSb.toString());
            sqlList.add("COMMENT ON COLUMN \"" + tableName + "\".\"" + name.toUpperCase() + "\" IS '" + comment + "'");
        }

        String[] sqlArr = sqlList.toArray(new String[0]);
        for (String str : sqlArr) {
            System.out.println(str);
        }
        jdbcTemplate.batchUpdate(sqlList.toArray(new String[0]));
        return R.success();
    }

}
